# -*- coding: utf-8 -*-
# 读写2003 excel
import xlrd
import re

def fdsql(field2,typ3,fcomment2):
    sql=" `"+field2+"` "
    if "字符型" in typ3:
        ln=re.sub("\D", "", typ3)
        tye3="VARCHAR("+ln+") NOT NULL DEFAULT '' "
    elif "数值型" in typ3:
        ln=re.sub("\D", "", typ3)
        inln=int(ln)
        if inln>15 or ("保留" in fcomment2) :
        	tye3="DECIMAL(16,2) NOT NULL DEFAULT 0.00 "
        else:
        	tye3="BIGINT("+ln+") NOT NULL DEFAULT 0 "
    else:
        tye3="未知"
    sql=sql+tye3
    sql=sql+'COMMENT'+"'"+fcomment2+"',\n"
    return sql;

workbook = xlrd.open_workbook("e:\\table.xlsx")
f = open("e:\\cr.sql", 'w')
names=workbook.sheet_names()
comment=''
field=''
typ=''
fcomment=''

for name in names:
   table = workbook.sheet_by_name(name) #通过名称获取  
   nrows = table.nrows
   print("-- -----------"+name)
   f.write("-- -----------"+name+"\n")
   csql="CREATE TABLE `"+name+"` (\n `id` BIGINT(20) NOT NULL  AUTO_INCREMENT,\n"
   for i in range(nrows):
      row = table.row_values(i)
      if i<2:
      	 if i==0:
      	 	comment=(table.row(0)[0].value).replace("\n", "").replace("\r","")
      else:
      	 j=0;
      	 for cell in row:
      	 	cell=str(cell).replace("\n", "").replace("\r","").strip()
      	 	if j==1:
      	 		field=str.lower(cell).replace(" seqno=”  ”","").strip()
      	 	if j==2:
      	 		typ=cell
      	 	if j==3:
      	 	    fcomment= cell
      	 	if j==4:
      	 		fcomment=fcomment+((" 定义:"+cell) if cell != "" else cell)
      	 	if j==5:
      	 		fcomment=fcomment+((" 规则:"+cell) if cell != "" else cell)
      	 	j=j+1
      	 #print(typ)
      	 csql=csql+fdsql(field,typ,fcomment)
      i=i+1
   csql=csql+" `data_source`  VARCHAR(50)  NOT NULL DEFAULT '' COMMENT '(自定义字段)数据来源' ,\n"
   csql=csql+" `is_delete` INTEGER(1) NOT NULL DEFAULT 0 COMMENT '(自定义字段)删除标记0未删除1已删除',\n"
   csql=csql+" `delete_cause` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '(自定义字段)删除原因',\n"
   csql=csql+" `create_time` DATETIME NOT NULL COMMENT '(自定义字段)创建时间',\n"
   csql=csql+" `update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '(自定义字段)最晚更新时间',\n"
   csql=csql+" PRIMARY KEY USING BTREE (`id`)\n ) ENGINE=InnoDB AUTO_INCREMENT=1 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci' COMMENT='"+comment+"';"
   print(csql)
   f.write(csql)
   #break;
#table = 
f.close()

